﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtlity;
using Models;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class PJSearchService
    {
        public List<ProjectMain> GetMain(PJSearch PJSearch)
        {
            string sql = "select * from PJMains";
            sql += " inner join WorkflowStatuses on WorkflowStatuses.WorkParentId=PJMains.WorkParentId";
            sql += " inner join UserInfoes on UserInfoes.UserName=WorkflowStatuses.CreaterUserName";
            sql += " where Statues=@Statues";
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@Statues", "1"));
            if (PJSearch.Id2 == -1)
            {
                sql += " and PJId like '%'+@PJId+'%'";
                param.Add(new SqlParameter("@PJId", PJSearch.PJId));
            }
            else
            {
                sql += " and Id>=@Id1 and Id<=@Id2";
                param.Add(new SqlParameter("@Id1", PJSearch.Id1));
                param.Add(new SqlParameter("@Id2", PJSearch.Id2));
            }
            if (!string.IsNullOrEmpty(PJSearch.PJName))
            {
                sql += " and PJName=@PJName";
                param.Add(new SqlParameter("@PJName", PJSearch.PJName));
            }
            if (!string.IsNullOrEmpty(PJSearch.CreaterName))
            {
                sql += " and DisplayName=@DisplayName";
                param.Add(new SqlParameter("@DisplayName", PJSearch.CreaterName));
            }
            if (PJSearch.PJType != "全部")
            {
                sql += " and PJType=@PJType";
                param.Add(new SqlParameter("@PJType", PJSearch.PJType));
            }
            if (PJSearch.MoldType != "全部")
            {
                sql += " and MoldType like '%'+@MoldType+'%'";
                param.Add(new SqlParameter("@MoldType", PJSearch.MoldType));
            }
            if (PJSearch.CreateTime1 != Convert.ToDateTime("1900-01-01"))
            {
                if (PJSearch.CreateTime2 != Convert.ToDateTime("1900-01-01"))
                {
                    sql += " and CreateTime>=@CreateTime1 and CreateTime<=@CreateTime2";
                    param.Add(new SqlParameter("@CreateTime2", PJSearch.CreateTime2));
                }
                else
                {
                    sql += " and CreateTime=@CreateTime1";
                }
                param.Add(new SqlParameter("@CreateTime1", PJSearch.CreateTime1));
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            List<ProjectMain> list = new List<ProjectMain>();
            while (objReader.Read())
            {
                list.Add(new ProjectMain()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    PJId = objReader["PJId"].ToString(),
                    PJName = objReader["PJName"].ToString(),
                    Mold = objReader["Mold"].ToString(),
                    Maktx = objReader["Maktx"].ToString(),
                    MaktxName = objReader["MaktxName"].ToString(),
                    Customer = objReader["Customer"].ToString(),
                    BusinessPlate = objReader["BusinessPlate"].ToString(),
                    PM = objReader["PM"].ToString(),
                    StartDate = Convert.ToDateTime(objReader["StartDate"]),
                    PJType = objReader["PJType"].ToString(),
                    MoldType = objReader["MoldType"].ToString(),
                    DisplayName = objReader["DisplayName"].ToString(),
                    T1EndDate = Convert.ToDateTime(objReader["T1EndDate"]),
                    T2EndDate = Convert.ToDateTime(objReader["T2EndDate"]),
                    T3EndDate = Convert.ToDateTime(objReader["T3EndDate"]),
                    T4EndDate = Convert.ToDateTime(objReader["T4EndDate"]),
                    T5EndDate = Convert.ToDateTime(objReader["T5EndDate"]),
                    T1RealEndDate = objReader["T1RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T1RealEndDate"]),
                    T2RealEndDate = objReader["T2RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T2RealEndDate"]),
                    T3RealEndDate = objReader["T3RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T3RealEndDate"]),
                    T4RealEndDate = objReader["T4RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T4RealEndDate"]),
                    T5RealEndDate = objReader["T5RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T5RealEndDate"]),
                    CreateTime = objReader["CreateTime"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["CreateTime"]),
                    StrT1EndDate = Convert.ToDateTime(objReader["T1EndDate"]).ToShortDateString(),
                    StrT2EndDate = Convert.ToDateTime(objReader["T2EndDate"]).ToShortDateString(),
                    StrT3EndDate = Convert.ToDateTime(objReader["T3EndDate"]).ToShortDateString(),
                    StrT4EndDate = Convert.ToDateTime(objReader["T4EndDate"]).ToShortDateString(),
                    StrT5EndDate = Convert.ToDateTime(objReader["T5EndDate"]).ToShortDateString(),
                    StrT1RealEndDate = objReader["T1RealEndDate"] is DBNull ? "" : Convert.ToDateTime(objReader["T1RealEndDate"]).ToShortDateString(),
                    StrT2RealEndDate = objReader["T2RealEndDate"] is DBNull ? "" : Convert.ToDateTime(objReader["T2RealEndDate"]).ToShortDateString(),
                    StrT3RealEndDate = objReader["T3RealEndDate"] is DBNull ? "" : Convert.ToDateTime(objReader["T3RealEndDate"]).ToShortDateString(),
                    StrT4RealEndDate = objReader["T4RealEndDate"] is DBNull ? "" : Convert.ToDateTime(objReader["T4RealEndDate"]).ToShortDateString(),
                    StrT5RealEndDate = objReader["T5RealEndDate"] is DBNull ? "" : Convert.ToDateTime(objReader["T5RealEndDate"]).ToShortDateString()
                });
            }
            objReader.Close();
            return list;
        }

        public List<ProjectMain> GetMain(string mold, string name, string createDate1, string createDate2, string[] pjTypes, string[] moldTypes, string[] labelTypes)
        {
            string sql = "select * from PJMains";
            sql += " inner join WorkflowStatuses on WorkflowStatuses.WorkParentId=PJMains.WorkParentId";
            sql += " inner join UserInfoes on UserInfoes.UserName=WorkflowStatuses.CreaterUserName";
            sql += " left join PJLogs on PJLogs.PJId=PJMains.PJId and PJLogs.Id=";
            sql += "(select max(id) from PJLogs where PJLogs.PJId =PJMains.PJId and PJLogs.Statues=@Statues)";
            sql += " where PJMains.Statues=@Statues";
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@Statues", "1"));
            if (!string.IsNullOrEmpty(mold))
            {
                sql += " and PJMains.PJId=@PJId";
                param.Add(new SqlParameter("@PJId", mold));
            }
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and DisplayName=@DisplayName";
                param.Add(new SqlParameter("@DisplayName", name));
            }
            if (!string.IsNullOrEmpty(createDate1) && !string.IsNullOrEmpty(createDate2))
            {
                sql += " and CreateTime>=@CreateDate1 and CreateTime<=@CreateDate2";
                param.Add(new SqlParameter("@CreateDate1", Convert.ToDateTime(createDate1)));
                param.Add(new SqlParameter("@CreateDate2", Convert.ToDateTime(createDate2)));
            }
            else if (!string.IsNullOrEmpty(createDate1) && string.IsNullOrEmpty(createDate2))
            {
                sql += " and CreateTime==@CreateDate1";
                param.Add(new SqlParameter("@CreateDate1", Convert.ToDateTime(createDate1)));
            }
            if (pjTypes[0].Length > 0)
            {
                for (int i = 0; i < pjTypes.Length; i++)
                {
                    if (i == 0)
                    {
                        sql += " and (PJType=@PJType" + i;
                        param.Add(new SqlParameter("@PJType" + i, pjTypes[i]));
                    }
                    else
                    {
                        sql += " or PJType=@PJType" + i;
                        param.Add(new SqlParameter("@PJType" + i, pjTypes[i]));
                    }
                    if (i == pjTypes.Length - 1)
                    {
                        sql += ")";
                    }
                }
            }
            if (moldTypes[0].Length > 0)
            {
                for (int i = 0; i < moldTypes.Length; i++)
                {
                    if (i == 0)
                    {

                        sql += " and (MoldType like  '%'+@MoldType" + i + "+'%'";
                        param.Add(new SqlParameter("@MoldType" + i, moldTypes[i]));
                    }
                    else
                    {
                        sql += " or MoldType like  '%'+@MoldType" + i + "+'%'";
                        param.Add(new SqlParameter("@MoldType" + i, moldTypes[i]));
                    }
                    if (i == moldTypes.Length - 1)
                    {
                        sql += ")";
                    }
                }
            }
            List<string> temps = new List<string>();
            for (int i = 0; i < labelTypes.Length; i++)
            {
                if (labelTypes[i] == "过期:未完成")
                {
                    temps.Add(" (T1EndDate<getdate() and T1RealEndDate is null or T2EndDate<getdate() and T2RealEndDate is null or T3EndDate<getdate() and T3RealEndDate is null or T4EndDate<getdate() and T4RealEndDate is null or T5EndDate<getdate() and T5RealEndDate is null)");
                }
                if (labelTypes[i] == "过期:完成")
                {
                    temps.Add(" (T1RealEndDate<=T1EndDate or T2RealEndDate<=T2EndDate or T3RealEndDate<=T3EndDate or T4RealEndDate<=T4EndDate or T5RealEndDate<=T5EndDate)");
                }
                if (labelTypes[i] == "临近2周提醒")
                {
                    temps.Add(" (T1EndDate - getdate()>=0 and T1EndDate - getdate()<=15 and T1RealEndDate is null or T2EndDate - getdate()>=0 and T2EndDate - getdate()<=15 and T2RealEndDate is null or T3EndDate - getdate()>=0 and T3EndDate - getdate()<=15 and T3RealEndDate is null or T4EndDate - getdate()>=0 and T4EndDate - getdate()<=15 and T4RealEndDate is null or T5EndDate - getdate()>=0 and T5EndDate - getdate()<=15 and T5RealEndDate is null)");
                }
                if (labelTypes[i] == "过期:延期完成")
                {
                    temps.Add(" (T1RealEndDate>T1EndDate or T2RealEndDate>T2EndDate or T3RealEndDate>T3EndDate or T4RealEndDate>T4EndDate or T5RealEndDate>T5EndDate)");
                }
            }
            if(temps.Count>0)
            {
                string temp = string.Join(" or ", temps.ToArray());
                sql += " and (" + temp + ")";
            }
            
            

            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            List<ProjectMain> list = new List<ProjectMain>();
            while (objReader.Read())
            {
                list.Add(new ProjectMain()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    PJId = objReader["PJId"].ToString(),
                    PJName = objReader["PJName"].ToString(),
                    Mold = objReader["Mold"].ToString(),
                    Maktx = objReader["Maktx"].ToString(),
                    MaktxName = objReader["MaktxName"].ToString(),
                    Customer = objReader["Customer"].ToString(),
                    BusinessPlate = objReader["BusinessPlate"].ToString(),
                    PM = objReader["PM"].ToString(),
                    StartDate = Convert.ToDateTime(objReader["StartDate"]),
                    PJType = objReader["PJType"].ToString(),
                    MoldType = objReader["MoldType"].ToString(),
                    DisplayName = objReader["DisplayName"].ToString(),
                    T1EndDate = Convert.ToDateTime(objReader["T1EndDate"]),
                    T2EndDate = Convert.ToDateTime(objReader["T2EndDate"]),
                    T3EndDate = Convert.ToDateTime(objReader["T3EndDate"]),
                    T4EndDate = Convert.ToDateTime(objReader["T4EndDate"]),
                    T5EndDate = Convert.ToDateTime(objReader["T5EndDate"]),
                    T1RealEndDate = objReader["T1RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T1RealEndDate"]),
                    T2RealEndDate = objReader["T2RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T2RealEndDate"]),
                    T3RealEndDate = objReader["T3RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T3RealEndDate"]),
                    T4RealEndDate = objReader["T4RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T4RealEndDate"]),
                    T5RealEndDate = objReader["T5RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T5RealEndDate"]),
                    CreateTime = objReader["CreateTime"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["CreateTime"]),
                    Ps = objReader["PS"].ToString(),
                    StrT1EndDate = Convert.ToDateTime(objReader["T1EndDate"]).ToString("yyyy-MM-dd"),
                    StrT2EndDate = Convert.ToDateTime(objReader["T2EndDate"]).ToString("yyyy-MM-dd"),
                    StrT3EndDate = Convert.ToDateTime(objReader["T3EndDate"]).ToString("yyyy-MM-dd"),
                    StrT4EndDate = Convert.ToDateTime(objReader["T4EndDate"]).ToString("yyyy-MM-dd"),
                    StrT5EndDate = Convert.ToDateTime(objReader["T5EndDate"]).ToString("yyyy-MM-dd"),
                    StrT1RealEndDate = objReader["T1RealEndDate"] is DBNull ? "" : Convert.ToDateTime(objReader["T1RealEndDate"]).ToString("yyyy-MM-dd"),
                    StrT2RealEndDate = objReader["T2RealEndDate"] is DBNull ? "" : Convert.ToDateTime(objReader["T2RealEndDate"]).ToString("yyyy-MM-dd"),
                    StrT3RealEndDate = objReader["T3RealEndDate"] is DBNull ? "" : Convert.ToDateTime(objReader["T3RealEndDate"]).ToString("yyyy-MM-dd"),
                    StrT4RealEndDate = objReader["T4RealEndDate"] is DBNull ? "" : Convert.ToDateTime(objReader["T4RealEndDate"]).ToString("yyyy-MM-dd"),
                    StrT5RealEndDate = objReader["T5RealEndDate"] is DBNull ? "" : Convert.ToDateTime(objReader["T5RealEndDate"]).ToString("yyyy-MM-dd"),
                    Problem = objReader["Problem"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        public int GetId(string pjId)
        {
            string sql = "select Id from PJMains where PJId=@PJId";
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, new SqlParameter[] { new SqlParameter("@PJId", pjId) }));
        }

        public List<PJLog> GetLog(string pjId)
        {
            string sql = "select * from PJLogs where PJId=@PJId and Statues=@Statues";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@PJId", pjId),
                new SqlParameter("@Statues", '1')
            };
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            List<PJLog> list = new List<PJLog>();
            while (objReader.Read())
            {
                list.Add(new PJLog()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    PJId = objReader["PJId"].ToString(),
                    Stage = objReader["Stage"].ToString(),
                    Node = objReader["Node"].ToString(),
                    Problem = objReader["Problem"].ToString(),
                    Source = objReader["Source"].ToString(),
                    Person = objReader["Person"].ToString(),
                    Department = objReader["Department"].ToString(),
                    Statues1 = objReader["Statues1"].ToString(),
                    Degree = objReader["Degree"].ToString(),
                    StrStartDate = objReader["StartDate"] is DBNull ? "" : Convert.ToDateTime(objReader["StartDate"]).ToString("yyyy-MM-dd"),
                    StrCompleteDate = objReader["CompleteDate"] is DBNull ? "" : Convert.ToDateTime(objReader["CompleteDate"]).ToString("yyyy-MM-dd"),
                    StrRealCompleteDate = objReader["RealCompleteDate"] is DBNull ? "" : Convert.ToDateTime(objReader["RealCompleteDate"]).ToString("yyyy-MM-dd"),
                    Ps = objReader["Ps"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        public List<PJLog> GetLog(string mold, string name, string createDate1, string createDate2, string[] degrees, string[] departments, string[] labelTypes)
        {
            string sql = "select * from PJLogs";
            sql += " inner join WorkflowStatuses on WorkflowStatuses.WorkParentId=PJLogs.WorkParentId";
            sql += " inner join UserInfoes on UserInfoes.UserName=WorkflowStatuses.CreaterUserName";
            sql += " where PJLogs.Statues=@Statues";
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@Statues", "1"));
            if (!string.IsNullOrEmpty(mold))
            {
                sql += " and PJLogs.PJId=@PJId";
                param.Add(new SqlParameter("@PJId", mold));
            }
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and DisplayName=@DisplayName";
                param.Add(new SqlParameter("@DisplayName", name));
            }
            if (!string.IsNullOrEmpty(createDate1) && !string.IsNullOrEmpty(createDate2))
            {
                sql += " and CreateTime>=@CreateDate1 and CreateTime<=@CreateDate2";
                param.Add(new SqlParameter("@CreateDate1", Convert.ToDateTime(createDate1)));
                param.Add(new SqlParameter("@CreateDate2", Convert.ToDateTime(createDate2)));
            }
            else if (!string.IsNullOrEmpty(createDate1) && string.IsNullOrEmpty(createDate2))
            {
                sql += " and CreateTime==@CreateDate1";
                param.Add(new SqlParameter("@CreateDate1", Convert.ToDateTime(createDate1)));
            }
            if (degrees[0].Length > 0)
            {
                for (int i = 0; i < degrees.Length; i++)
                {
                    if (i == 0)
                    {
                        sql += " and (Degree=@Degree" + i;
                        param.Add(new SqlParameter("@Degree" + i, degrees[i]));
                    }
                    else
                    {
                        sql += " or Degree=@Degree" + i;
                        param.Add(new SqlParameter("@Degree" + i, degrees[i]));
                    }
                    if (i == degrees.Length - 1)
                    {
                        sql += ")";
                    }
                }
            }
            if (departments[0].Length > 0)
            {
                for (int i = 0; i < departments.Length; i++)
                {
                    if (i == 0)
                    {

                        sql += " and (Department like  '%'+@Department" + i + "+'%'";
                        param.Add(new SqlParameter("@Department" + i, departments[i]));
                    }
                    else
                    {
                        sql += " or Department like  '%'+@Department" + i + "+'%'";
                        param.Add(new SqlParameter("@Department" + i, departments[i]));
                    }
                    if (i == departments.Length - 1)
                    {
                        sql += ")";
                    }
                }
            }
            List<string> temps = new List<string>();
            for (int i = 0; i < labelTypes.Length; i++)
            {
                if (labelTypes[i] == "过期:未完成")
                {
                    temps.Add(" CompleteDate<getdate() and RealCompleteDate=@date1");
                    param.Add(new SqlParameter("@date1", "1900-01-01"));
                }
                if (labelTypes[i] == "过期:完成")
                {
                    temps.Add(" RealCompleteDate<=CompleteDate");
                }
                if (labelTypes[i] == "临近2周提醒")
                {
                    temps.Add(" CompleteDate - getdate()>=0 and CompleteDate - getdate()<=15 and RealCompleteDate=@date2");
                    param.Add(new SqlParameter("@date2", "1900-01-01"));
                }
                if (labelTypes[i] == "过期:延期完成")
                {
                    temps.Add(" RealCompleteDate>CompleteDate");
                }
            }
            if (temps.Count > 0)
            {
                string temp = string.Join(" or ", temps.ToArray());
                sql += " and (" + temp + ")";
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            List<PJLog> list = new List<PJLog>();
            while (objReader.Read())
            {
                list.Add(new PJLog()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    PJId = objReader["PJId"].ToString(),
                    DisplayName = objReader["DisplayName"].ToString(),
                    Department = objReader["Department"].ToString(),
                    Degree = objReader["Degree"].ToString(),
                    StrStartDate = objReader["StartDate"].ToString() == "1900-01-01" ? "" : Convert.ToDateTime(objReader["StartDate"]).ToString("yyyy-MM-dd"),
                    StrCompleteDate = objReader["CompleteDate"].ToString() == "1900-01-01" ? "" : Convert.ToDateTime(objReader["CompleteDate"]).ToString("yyyy-MM-dd"),
                    StrRealCompleteDate = objReader["RealCompleteDate"].ToString() == "1900-01-01" ? "" : Convert.ToDateTime(objReader["RealCompleteDate"]).ToString("yyyy-MM-dd"),
                    Problem = objReader["Problem"].ToString(),
                    Ps2 = objReader["Ps2"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        public ProjectMain GetMain(string pjId)
        {
            string sql = "select * from PJMains";
            //sql += " inner join WorkflowStatuses on WorkflowStatuses.WorkParentId=PJMains.WorkParentId";
            //sql += " inner join UserInfoes on UserInfoes.UserName=WorkflowStatuses.CreaterUserName";
            sql += " where Statues=@Statues and PJId=@PJId";

            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Statues", "1"),
                new SqlParameter("@PJId",pjId)
            };

            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            ProjectMain PJMain = null;
            if (objReader.Read())
            {
                PJMain = new ProjectMain()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    PJId = objReader["PJId"].ToString(),
                    PJName = objReader["PJName"].ToString(),
                    Mold = objReader["Mold"].ToString(),
                    Maktx = objReader["Maktx"].ToString(),
                    MaktxName = objReader["MaktxName"].ToString(),
                    Customer = objReader["Customer"].ToString(),
                    BusinessPlate = objReader["BusinessPlate"].ToString(),
                    PM = objReader["PM"].ToString(),
                    StartDate = Convert.ToDateTime(objReader["StartDate"]),
                    PJType = objReader["PJType"].ToString(),
                    MoldType = objReader["MoldType"].ToString()
                    //DisplayName = objReader["DisplayName"].ToString(),
                    //T1EndDate = Convert.ToDateTime(objReader["T1EndDate"]),
                    //T2EndDate = Convert.ToDateTime(objReader["T2EndDate"]),
                    //T3EndDate = Convert.ToDateTime(objReader["T3EndDate"]),
                    //T4EndDate = Convert.ToDateTime(objReader["T4EndDate"]),
                    //T5EndDate = Convert.ToDateTime(objReader["T5EndDate"]),
                    //T1RealEndDate = objReader["T1RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T1RealEndDate"]),
                    //T2RealEndDate = objReader["T2RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T2RealEndDate"]),
                    //T3RealEndDate = objReader["T3RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T3RealEndDate"]),
                    //T4RealEndDate = objReader["T4RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T4RealEndDate"]),
                    //T5RealEndDate = objReader["T5RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T5RealEndDate"])
                };
            }
            objReader.Close();
            return PJMain;
        }
        /// <summary>
        /// 根据模具号查找节点
        /// </summary>
        /// <param name="pjId"></param>
        /// <returns></returns>
        public List<PJNode> GetNode(string pjId)
        {
            string sql = "select * from PJNodes where PJId=@PJId and Statues =@Statues";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@PJId",pjId),
                new SqlParameter("@Statues","1")
            };
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            List<PJNode> list = new List<PJNode>();
            while (objReader.Read())
            {
                list.Add(new PJNode()
                    {
                        Id = Convert.ToInt32(objReader["Id"]),
                        PJId = objReader["PJId"].ToString(),
                        Number = Convert.ToInt32(objReader["Number"]),
                        NodeName = objReader["NodeName"].ToString(),
                        CompleteDate = objReader["CompleteDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["CompleteDate"]),
                        RealCompleteDate = objReader["RealCompleteDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["RealCompleteDate"]),
                        Statues = objReader["Statues"].ToString(),
                        Content = objReader["Content"].ToString()
                    });
            }
            return list;
        }
        /// <summary>
        /// 节点状态查询
        /// </summary>
        /// <param name="PJSearch"></param>
        /// <returns></returns>
        public List<PJNodeShow> GetNode(PJSearch PJSearch)
        {
            //string sql = "SELECT t1.PJId,DisplayName,t1.[FOT] as [P1],t1.[OTS] as [P2],t1.[T/R] as [P3],";
            //sql += "t1.[PPAP] as [P4],t1.[进入爬坡] as [P5],t1.[退出爬坡] as [P6],t2.[FOT] as [R1],";
            //sql += "t2.[OTS] as [R2],t2.[T/R] as [R3],t2.[PPAP] as [R4],t2.[进入爬坡] as [R5],t2.[退出爬坡] as [R6]";
            //sql += " FROM(SELECT * FROM(select PJId,CompleteDate,NodeName,Statues,WorkParentId from PJNodes)";
            //sql += " AS A pivot( max(CompleteDate) for NodeName in([FOT],[OTS],[T/R],[PPAP],[进入爬坡],[退出爬坡])) AS P) as t1";
            //sql += " inner join WorkflowStatuses on WorkflowStatuses.WorkParentId=t1.WorkParentId";
            //sql += " inner join UserInfoes on UserInfoes.UserName=WorkflowStatuses.CreaterUserName,";
            //sql += "(SELECT * FROM(select PJId,NodeName,RealCompleteDate from PJNodes)";
            //sql += " AS A pivot( max(RealCompleteDate) for NodeName in([FOT],[OTS],[T/R],[PPAP],[进入爬坡],[退出爬坡])) AS P1 ) as t2 ";
            string sql = "SELECT t1.PJId,DisplayName,t1.[FOT] as [P1],t1.[OTS] as [P2],t1.[T/R] as [P3],";
            sql += "t1.[PPAP] as [P4],t1.[PSW] as [P5],t1.[进入爬坡] as [P6],t1.[退出爬坡] as [P7],t2.[FOT] as [R1],";
            sql += "t2.[OTS] as [R2],t2.[T/R] as [R3],t2.[PPAP] as [R4],t2.[PSW] as [R5],t2.[进入爬坡] as [R6],t2.[退出爬坡] as [R7]";
            sql += " FROM(SELECT * FROM(select PJId,CompleteDate,NodeName,Statues,WorkParentId from PJNodes)";
            sql += " AS A pivot( max(CompleteDate) for NodeName in([FOT],[OTS],[T/R],[PPAP],[PSW],[进入爬坡],[退出爬坡])) AS P) as t1";
            sql += " inner join WorkflowStatuses on WorkflowStatuses.WorkParentId=t1.WorkParentId";
            sql += " inner join UserInfoes on UserInfoes.UserName=WorkflowStatuses.CreaterUserName,";
            sql += "(SELECT * FROM(select PJId,NodeName,RealCompleteDate from PJNodes)";
            sql += " AS A pivot( max(RealCompleteDate) for NodeName in([FOT],[OTS],[T/R],[PSW],[PPAP],[进入爬坡],[退出爬坡])) AS P1 ) as t2 ";


            sql += " where t1.Pjid = t2.Pjid and t1.Statues=@Statues";
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@Statues", "1"));
            if (!string.IsNullOrEmpty(PJSearch.PJId))
            {
                sql += " and t1.PJId like '%'+@PJId+'%'";
                param.Add(new SqlParameter("@PJId", PJSearch.PJId));
            }
            if (!string.IsNullOrEmpty(PJSearch.CreaterName))
            {
                sql += " and UserInfoes.DisplayName=@DisplayName";
                param.Add(new SqlParameter("@DisplayName", PJSearch.CreaterName));
            }
            if (PJSearch.CreateTime1 != Convert.ToDateTime("1900-01-01"))
            {
                if (PJSearch.CreateTime2 != Convert.ToDateTime("1900-01-01"))
                {
                    sql += " and CreateTime>=@CreateTime1 and CreateTime<=@CreateTime2";
                    param.Add(new SqlParameter("@CreateTime2", PJSearch.CreateTime2));
                }
                else
                {
                    sql += " and CreateTime=@CreateTime1";
                }
                param.Add(new SqlParameter("@CreateTime1", PJSearch.CreateTime1));
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            List<PJNodeShow> list = new List<PJNodeShow>();
            while (objReader.Read())
            {
                list.Add(new PJNodeShow()
                    {
                        PJId = objReader["PJId"].ToString(),
                        DisplayName = objReader["DisplayName"].ToString(),
                        CompleteDate1 = objReader["P1"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["P1"]),
                        CompleteDate2 = objReader["P2"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["P2"]),
                        CompleteDate3 = objReader["P3"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["P3"]),
                        CompleteDate4 = objReader["P4"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["P4"]),
                        CompleteDate5 = objReader["P5"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["P5"]),
                        CompleteDate6 = objReader["P6"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["P6"]),
                        CompleteDate7 = objReader["P7"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["P7"]),
                        RealCompleteDate1 = objReader["R1"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["R1"]),
                        RealCompleteDate2 = objReader["R2"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["R2"]),
                        RealCompleteDate3 = objReader["R3"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["R3"]),
                        RealCompleteDate4 = objReader["R4"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["R4"]),
                        RealCompleteDate5 = objReader["R5"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["R5"]),
                        RealCompleteDate6 = objReader["R6"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["R6"]),
                        RealCompleteDate7 = objReader["R7"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["R7"])
                    });
            }
            objReader.Close();
            return list;

        }
        public List<PJNodeShow> GetNode(string mold, string name, string createDate1, string createDate2, string[] pjTypes, string[] moldTypes, string[] labelTypes)
        {
            string sql = "SELECT t1.PJId,DisplayName,PJMains.PJType,PJMains.MoldType,t1.[FOT] as [P1],t1.[OTS] as [P2],t1.[T/R] as [P3],";
            sql += "t1.[PPAP] as [P4],t1.[PSW] as [P5],t1.[进入爬坡] as [P6],t1.[退出爬坡] as [P7],t2.[FOT] as [R1],";
            sql += "t2.[OTS] as [R2],t2.[T/R] as [R3],t2.[PPAP] as [R4],t2.[PSW] as [R5],t2.[进入爬坡] as [R6],t2.[退出爬坡] as [R7],PJLogs.Problem,t1.Ps";
            sql += " FROM(SELECT * FROM(select PJId,CompleteDate,NodeName,Statues,WorkParentId,Ps from PJNodes)";
            sql += " AS A pivot( max(CompleteDate) for NodeName in([FOT],[OTS],[T/R],[PPAP],[PSW],[进入爬坡],[退出爬坡])) AS P) as t1";
            sql += " inner join WorkflowStatuses on WorkflowStatuses.WorkParentId=t1.WorkParentId";
            sql += " inner join UserInfoes on UserInfoes.UserName=WorkflowStatuses.CreaterUserName";
            sql += " inner join PJMains on t1.PJId=PJMains.PJId and PJMains.Statues=@Statues";
            sql += " left join PJLogs on PJLogs.PJId=t1.PJId and PJLogs.Id=(select max(id) from PJLogs where PJLogs.PJId =t1.PJId and PJLogs.Statues=@Statues),";
            sql += "(SELECT * FROM(select PJId,NodeName,RealCompleteDate from PJNodes)";
            sql += " AS A pivot( max(RealCompleteDate) for NodeName in([FOT],[OTS],[T/R],[PSW],[PPAP],[进入爬坡],[退出爬坡])) AS P1 ) as t2 ";
            sql += " where t1.Pjid = t2.Pjid and t1.Statues=@Statues";
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@Statues", "1"));
            if (!string.IsNullOrEmpty(mold))
            {
                sql += " and t1.PJId=@PJId";
                param.Add(new SqlParameter("@PJId", mold));
            }
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and DisplayName=@DisplayName";
                param.Add(new SqlParameter("@DisplayName", name));
            }
            if (!string.IsNullOrEmpty(createDate1) && !string.IsNullOrEmpty(createDate2))
            {
                sql += " and CreateTime>=@CreateDate1 and CreateTime<=@CreateDate2";
                param.Add(new SqlParameter("@CreateDate1", Convert.ToDateTime(createDate1)));
                param.Add(new SqlParameter("@CreateDate2", Convert.ToDateTime(createDate2)));
            }
            else if (!string.IsNullOrEmpty(createDate1) && string.IsNullOrEmpty(createDate2))
            {
                sql += " and CreateTime==@CreateDate1";
                param.Add(new SqlParameter("@CreateDate1", Convert.ToDateTime(createDate1)));
            }
            if (pjTypes[0].Length > 0)
            {
                for (int i = 0; i < pjTypes.Length; i++)
                {
                    if (i == 0)
                    {
                        sql += " and (PJType=@PJType" + i;
                        param.Add(new SqlParameter("@PJType" + i, pjTypes[i]));
                    }
                    else
                    {
                        sql += " or PJType=@PJType" + i;
                        param.Add(new SqlParameter("@PJType" + i, pjTypes[i]));
                    }
                    if (i == pjTypes.Length - 1)
                    {
                        sql += ")";
                    }
                }
            }
            if (moldTypes[0].Length > 0)
            {
                for (int i = 0; i < moldTypes.Length; i++)
                {
                    if (i == 0)
                    {
                        sql += " and (MoldType like  '%'+@MoldType" + i + "+'%'";
                        param.Add(new SqlParameter("@MoldType" + i, moldTypes[i]));
                    }
                    else
                    {
                        sql += " or MoldType like  '%'+@MoldType" + i + "+'%'";
                        param.Add(new SqlParameter("@MoldType" + i, moldTypes[i]));
                    }
                    if (i == moldTypes.Length - 1)
                    {
                        sql += ")";
                    }
                }
            }
            string temp1 = "";
            string temp2 = "";
            string temp3 = "";
            string temp4 = "";
            for (int i = 0; i < labelTypes.Length; i++)
            {
                if (labelTypes[i] == "过期:未完成")
                {
                    temp1 += " (t1.[FOT]<getdate() and t2.[FOT]=@date1";
                    temp1 += " or t1.[OTS]<getdate() and t2.[OTS]=@date1";
                    temp1 += " or t1.[T/R]<getdate() and t2.[T/R]=@date1";
                    temp1 += " or t1.[PPAP]<getdate() and t2.[PPAP]=@date1";
                    temp1 += " or t1.[PSW]<getdate() and t2.[PSW]=@date1";
                    temp1 += " or t1.[进入爬坡]<getdate() and t2.[进入爬坡]=@date1";
                    temp1 += " or t1.[退出爬坡]<getdate() and t2.[退出爬坡]=@date1)";
                    param.Add(new SqlParameter("@date1", "1900-01-01"));
                }
                if (labelTypes[i] == "过期:完成")
                {
                    temp2 += " (t2.[FOT]<=t1.[FOT] and t2.[FOT]<>@date2";
                    temp2 += " or t2.[OTS]<=t1.[OTS] and t2.[OTS]<>@date2";
                    temp2 += " or t2.[T/R]<=t1.[T/R] and t2.[T/R]<>@date2";
                    temp2 += " or t2.[PPAP]<=t1.[PPAP] and t2.[PPAP]<>@date2";
                    temp2 += " or t2.[PSW]<=t1.[PSW] and t2.[PSW]<>@date2";
                    temp2 += " or t2.[进入爬坡]<=t1.[进入爬坡] and t2.[进入爬坡]<>@date2";
                    temp2 += " or t2.[退出爬坡]<=t1.[退出爬坡] and t2.[退出爬坡]<>@date2)";
                    param.Add(new SqlParameter("@date2", "1900-01-01"));
                }
                if (labelTypes[i] == "临近2周提醒")
                {
                    temp3 += " (t1.[FOT] - getdate()>=0 and t1.[FOT] - getdate()<=15 and t2.[FOT]=@date3";
                    temp3 += " or t1.[OTS] - getdate()>=0 and t1.[OTS] - getdate()<=15 and t2.[OTS]=@date3";
                    temp3 += " or t1.[T/R] - getdate()>=0 and t1.[T/R] - getdate()<=15 and t2.[T/R]=@date3";
                    temp3 += " or t1.[PPAP] - getdate()>=0 and t1.[PPAP] - getdate()<=15 and t2.[PPAP]=@date3";
                    temp3 += " or t1.[PSW] - getdate()>=0 and t1.[PSW] - getdate()<=15 and t2.[PSW]=@date3";
                    temp3 += " or t1.[进入爬坡] - getdate()>=0 and t1.[进入爬坡] - getdate()<=15 and t2.[进入爬坡]=@date3";
                    temp3 += " or t1.[退出爬坡] - getdate()>=0 and t1.[退出爬坡] - getdate()<=15 and t2.[退出爬坡]=@date3)";
                    param.Add(new SqlParameter("@date3", "1900-01-01"));
                }
                if (labelTypes[i] == "过期:延期完成")
                {
                    temp4 += " (t2.[FOT]>t1.[FOT]";
                    temp4 += " or t2.[OTS]>t1.[OTS]";
                    temp4 += " or t2.[T/R]>t1.[T/R]";
                    temp4 += " or t2.[PPAP]>t1.[PPAP]";
                    temp4 += " or t2.[PSW]>t1.[PSW]";
                    temp4 += " or t2.[进入爬坡]>t1.[进入爬坡]";
                    temp4 += " or t2.[退出爬坡]>t1.[退出爬坡])";
                }
            }
            if (temp1.Length > 0)
            {
                sql += " and" + temp1;
            }
            if (temp2.Length > 0)
            {
                sql += " and" + temp2;
            }
            if (temp3.Length > 0)
            {
                sql += " and" + temp3;
            }
            if (temp4.Length > 0)
            {
                sql += " and" + temp4;
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            List<PJNodeShow> list = new List<PJNodeShow>();
            while (objReader.Read())
            {
                list.Add(new PJNodeShow()
                {
                    PJId = objReader["PJId"].ToString(),
                    DisplayName = objReader["DisplayName"].ToString(),
                    StrCompleteDate1 = objReader["P1"].ToString() == "1900-01-01" ? "" : Convert.ToDateTime(objReader["P1"]).ToString("yyyy-MM-dd"),
                    StrCompleteDate2 = objReader["P2"].ToString() == "1900-01-01" ? "" : Convert.ToDateTime(objReader["P2"]).ToString("yyyy-MM-dd"),
                    StrCompleteDate3 = objReader["P3"].ToString() == "1900-01-01" ? "" : Convert.ToDateTime(objReader["P3"]).ToString("yyyy-MM-dd"),
                    StrCompleteDate4 = objReader["P4"].ToString() == "1900-01-01" ? "" : Convert.ToDateTime(objReader["P4"]).ToString("yyyy-MM-dd"),
                    StrCompleteDate5 = objReader["P5"].ToString() == "1900-01-01" ? "" : Convert.ToDateTime(objReader["P5"]).ToString("yyyy-MM-dd"),
                    StrCompleteDate6 = objReader["P6"].ToString() == "1900-01-01" ? "" : Convert.ToDateTime(objReader["P6"]).ToString("yyyy-MM-dd"),
                    StrCompleteDate7 = objReader["P7"].ToString() == "1900-01-01" ? "" : Convert.ToDateTime(objReader["P7"]).ToString("yyyy-MM-dd"),
                    StrRealCompleteDate1 = Convert.ToDateTime(objReader["R1"]).ToString("yyyy-MM-dd") == "1900-01-01" ? "" : Convert.ToDateTime(objReader["R1"]).ToString("yyyy-MM-dd"),
                    StrRealCompleteDate2 = Convert.ToDateTime(objReader["R2"]).ToString("yyyy-MM-dd") == "1900-01-01" ? "" : Convert.ToDateTime(objReader["R2"]).ToString("yyyy-MM-dd"),
                    StrRealCompleteDate3 = Convert.ToDateTime(objReader["R3"]).ToString("yyyy-MM-dd") == "1900-01-01" ? "" : Convert.ToDateTime(objReader["R3"]).ToString("yyyy-MM-dd"),
                    StrRealCompleteDate4 = Convert.ToDateTime(objReader["R4"]).ToString("yyyy-MM-dd") == "1900-01-01" ? "" : Convert.ToDateTime(objReader["R4"]).ToString("yyyy-MM-dd"),
                    StrRealCompleteDate5 = Convert.ToDateTime(objReader["R5"]).ToString("yyyy-MM-dd") == "1900-01-01" ? "" : Convert.ToDateTime(objReader["R5"]).ToString("yyyy-MM-dd"),
                    StrRealCompleteDate6 = Convert.ToDateTime(objReader["R6"]).ToString("yyyy-MM-dd") == "1900-01-01" ? "" : Convert.ToDateTime(objReader["R6"]).ToString("yyyy-MM-dd"),
                    StrRealCompleteDate7 = Convert.ToDateTime(objReader["R7"]).ToString("yyyy-MM-dd") == "1900-01-01" ? "" : Convert.ToDateTime(objReader["R7"]).ToString("yyyy-MM-dd"),
                    Ps = objReader["Ps"] is DBNull ? "" : objReader["Ps"].ToString(),
                    Problem = objReader["Problem"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        public List<PJOrderStart> GetOrderStart(string matnr, string person, string mold)
        {
            string sql = "select * from PJOrderStarts left join PJOrderChildStarts";
            sql += " on PJOrderStarts.SerialNum=PJOrderChildStarts.SerialNum";
            sql += " where PJOrderStarts.Statues=@Statues";
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@Statues", "1"));
            if (!string.IsNullOrEmpty(matnr))
            {
                sql += " and Matnr like '%'+@Matnr+'%'";
                param.Add(new SqlParameter("@Matnr", matnr));
            }
            if (!string.IsNullOrEmpty(person))
            {
                sql += " and Person like '%'+@Person+'%'";
                param.Add(new SqlParameter("@Person", person));
            }
            if (!string.IsNullOrEmpty(mold))
            {
                sql += " and Mold like '%'+@Mold+'%'";
                param.Add(new SqlParameter("@Mold", mold));
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            List<PJOrderStart> list = new List<PJOrderStart>();
            while (objReader.Read())
            {
                list.Add(new PJOrderStart()
                {
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    SerialNum = objReader["SerialNum"].ToString(),
                    OrderType = objReader["OrderType"].ToString(),
                    OrderPurpose = objReader["OrderPurpose"].ToString(),
                    Person = objReader["Person"].ToString(),
                    Row = Convert.ToInt32(objReader["Row"]),
                    CustOrDepart = objReader["CustOrDepart"].ToString(),
                    CustomerOrder = objReader["CustomerOrder"].ToString(),
                    Maktx = objReader["Maktx"].ToString(),
                    Matnr = objReader["Matnr"].ToString(),
                    Number = Convert.ToInt32(objReader["Number"]),
                    Mold = objReader["Mold"].ToString(),
                    ConfirmDate = objReader["ConfirmDate"] is DBNull || Convert.ToDateTime(objReader["ConfirmDate"]) == Convert.ToDateTime("1900-01-01") ? "" : Convert.ToDateTime(objReader["ConfirmDate"]).ToShortDateString(),
                    SapOrder = objReader["SapOrder"].ToString()
                });
            }
            objReader.Close();
            return list;
        }

        public List<PJSampleOut> GetSampleOut(string matnr)
        {
            string sql = "select * from PJSampleOuts left join PJSampleChildOuts";
            sql += " on PJSampleOuts.SerialNum=PJSampleChildOuts.SerialNum";
            sql += " where Statues=@Statues";
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@Statues", "1"));
            if (!string.IsNullOrEmpty(matnr))
            {
                sql += " and Rev like '%'+@Matnr+'%'";
                param.Add(new SqlParameter("@Matnr", matnr));
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            List<PJSampleOut> list = new List<PJSampleOut>();
            while (objReader.Read())
            {
                list.Add(new PJSampleOut()
                {
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    SerialNum = objReader["SerialNum"].ToString(),
                    OrderType = objReader["OrderType"].ToString(),
                    OrderStartSerialNum = objReader["OrderStartSerialNum"].ToString(),
                    StrOutDate = objReader["OutDate"] is DBNull || Convert.ToDateTime(objReader["OutDate"]) == Convert.ToDateTime("1900-01-01") ? "" : Convert.ToDateTime(objReader["OutDate"]).ToShortDateString(),
                    Customer = objReader["Customer"].ToString(),
                    CustomerAddress = objReader["CustomerAddress"].ToString(),
                    Person = objReader["Person"].ToString(),
                    SaleOrder = objReader["SaleOrder"] is DBNull ? "" : objReader["SaleOrder"].ToString(),
                    Bill = objReader["Bill"] is DBNull ? "" : objReader["Bill"].ToString(),
                    Bill2 = objReader["Bill2"] is DBNull ? "" : objReader["Bill2"].ToString(),
                    CustomerService = objReader["CustomerService"].ToString(),
                    ExpressNo = objReader["ExpressNo"].ToString(),
                    Tel = objReader["Tel"].ToString(),
                    Row = Convert.ToInt32(objReader["Row"]),
                    CustomerOrder = objReader["CustomerOrder"].ToString(),
                    Maktx = objReader["Maktx"].ToString(),
                    Description = objReader["Description"].ToString(),
                    Num = Convert.ToInt32(objReader["Num"]),
                    Rev = objReader["Rev"].ToString(),
                    Ps = objReader["Ps"].ToString()
                });
            }
            objReader.Close();
            return list;
        }

        public List<PJNode> GetNode(string pjId, string displayName, DateTime date1, DateTime date2)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            string sql = "select * from NodeFOT where 1=1";
            if (!string.IsNullOrEmpty(pjId))
            {
                sql += " and PJId like '%'+@PJId+'%'";
            }
            if (!string.IsNullOrEmpty(displayName))
            {
                sql += " and DisplayName=@DisplayName";
            }
            sql += " and CompleteDate>=@date1 and CompleteDate<=@date2 union all";
            sql += " select * from NodeOTS where 1=1";
            if (!string.IsNullOrEmpty(pjId))
            {
                sql += " and PJId like '%'+@PJId+'%'";
            }
            if (!string.IsNullOrEmpty(displayName))
            {
                sql += " and DisplayName=@DisplayName";
            }
            sql += " and CompleteDate>=@date1 and CompleteDate<=@date2 union all";
            sql += " select * from NodePPAP where 1=1";
            if (!string.IsNullOrEmpty(pjId))
            {
                sql += " and PJId like '%'+@PJId+'%'";
                param.Add(new SqlParameter("@PJId", pjId));
            }
            if (!string.IsNullOrEmpty(displayName))
            {
                sql += " and DisplayName=@DisplayName";
                param.Add(new SqlParameter("@DisplayName", displayName));
            }
            sql += " and CompleteDate>=@Date1 and CompleteDate<=@Date2";
            param.Add(new SqlParameter("@Date1", date1));
            param.Add(new SqlParameter("@Date2", date2));
            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            List<PJNode> list = new List<PJNode>();
            while (objReader.Read())
            {
                list.Add(new PJNode()
                    {
                        PJId = objReader["PJId"].ToString(),
                        DisplayName = objReader["DisplayName"].ToString(),
                        NodeName = objReader["NodeName"].ToString(),
                        CompleteDate = objReader["CompleteDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["CompleteDate"]),
                        RealCompleteDate = objReader["RealCompleteDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["RealCompleteDate"])
                    });
            }
            return list;
        }

        public int UpdatePJMainsPs(string pjId, string ps)
        {
            string sql = "update PJMains set PS=@PS where PJId=@PJId and Statues=@Statues";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@PS",ps),
                new SqlParameter("@PJId",pjId),
                new SqlParameter("@Statues","1")
            };
            return SQLHelper.Update(sql, param);
        }
        public int UpdatePJNodesPs(string pjId, string ps)
        {
            string sql = "update PJNodes set Ps=@Ps where PJId=@PJId and Statues=@Statues";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Ps",ps),
                new SqlParameter("@PJId",pjId),
                new SqlParameter("@Statues","1")
            };
            return SQLHelper.Update(sql, param);
        }
        public int UpdatePJLogsPs(string id, string ps2)
        {
            string sql = "update PJLogs set Ps2=@Ps2 where Id=@Id and Statues=@Statues";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Ps2",ps2),
                new SqlParameter("@Id",id),
                new SqlParameter("@Statues","1")
            };
            return SQLHelper.Update(sql, param);
        }
        public string SearchPJMainsPs(string pjId)
        {
            string sql = "select PS from PJMains where PJId=@PJId and Statues=@Statues";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@PJId",pjId),
                new SqlParameter("@Statues","1")
            };
            return Convert.ToString(SQLHelper.GetSingleResult(sql, param));
        }
        public string SearchPJNodesPs(string pjId)
        {
            string sql = "select PS from PJNodes where PJId=@PJId and Statues=@Statues";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@PJId",pjId),
                new SqlParameter("@Statues","1")
            };
            return Convert.ToString(SQLHelper.GetSingleResult(sql, param));
        }
        public string SearchPJLogsPs(string id)
        {
            string sql = "select Ps2 from PJLogs where Id=@Id and Statues=@Statues";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Id",id),
                new SqlParameter("@Statues","1")
            };
            return Convert.ToString(SQLHelper.GetSingleResult(sql, param));
        }
    }
}
